package topcbl.database;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Locale;

import topcbl.data.FavoritePlace;
import topcbl.data.HistoryPlace;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TaxiDatabase extends SQLiteOpenHelper {
	// database version
	private static final int DATABASE_VERSION = 1;
	// database name
	private static final String DATABASE_NAME = "smartTaxiDB";
	// table name
	private static final String TABLE_NAME_FAV = "favorite";
	private static final String TABLE_NAME_HIS = "history";
	// table columns names
	public static final String COLUMN_ID = "id";
	public static final String COLUMN_NAME = "name";
	public static final String COLUMN_ADDRESS = "address";
	public static final String COLUMN_NUMBER = "number";
	public static final String COLUMN_DES = "description";
	public static final String COLUMN_TIME = "time";
	public static final String COLUMN_LAT = "lat";
	public static final String COLUMN_LNG = "lng";

	public TaxiDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + TABLE_NAME_FAV + "(" + COLUMN_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_NAME + ","
				+ COLUMN_ADDRESS + " TEXT NOT NULL, " + COLUMN_NUMBER + ","
				+ COLUMN_DES + "," + COLUMN_LAT + "," + COLUMN_LNG + ");");
		db.execSQL("CREATE TABLE " + TABLE_NAME_HIS + "(" + COLUMN_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + COLUMN_ADDRESS
				+ " TEXT NOT NULL, " + COLUMN_NUMBER + "," + COLUMN_DES + ","
				+ COLUMN_TIME + " TEXT NOT NULL," + COLUMN_LAT + ","
				+ COLUMN_LNG + ");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME_FAV);
		db.execSQL("DROP TABLE IF EXISTS" + TABLE_NAME_HIS);
		// create table again
		onCreate(db);
	}

	// --- manipulate favorite table ---

	public void addFavorite(String name, String address, String number,
			String des, double lat, double lng) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(COLUMN_NAME, name);
		values.put(COLUMN_ADDRESS, address);
		values.put(COLUMN_NUMBER, number);
		values.put(COLUMN_DES, des);
		values.put(COLUMN_LAT, lat);
		values.put(COLUMN_LNG, lng);
		db.insert(TABLE_NAME_FAV, null, values);
	}

	public void deleteFavorite(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME_FAV, "id=?", new String[] { String.valueOf(id) });
	}

	public ArrayList<FavoritePlace> getAllFavorite() {
		SQLiteDatabase db = this.getReadableDatabase();
		ArrayList<FavoritePlace> listPlace = new ArrayList<FavoritePlace>();
		Cursor cur = db.query(TABLE_NAME_FAV, null, null, null, null, null,
				null);
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			listPlace.add(new FavoritePlace(cur.getInt(0), cur.getString(1),
					cur.getString(2), cur.getInt(3), cur.getString(4), cur
							.getDouble(5), cur.getDouble(6)));
			cur.moveToNext();
		}
		return listPlace;
	}

	// --- manipulate history table ---

	public void addHistory(String address, int number, String des, double lat,
			double lng) {
		Calendar date = Calendar.getInstance();
		SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy hh:mm:ss",
				Locale.ENGLISH);
		String time = format.format(date.getTime());
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(COLUMN_ADDRESS, address);
		values.put(COLUMN_NUMBER, number);
		values.put(COLUMN_DES, des);
		values.put(COLUMN_TIME, time);
		values.put(COLUMN_LAT, lat);
		values.put(COLUMN_LNG, lng);
		db.insert(TABLE_NAME_HIS, null, values);
	}

	public void deleteHistory(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME_HIS, "id = ?", new String[] { String.valueOf(id) });
	}

	public ArrayList<HistoryPlace> getAllHistory() {
		SQLiteDatabase db = this.getReadableDatabase();
		ArrayList<HistoryPlace> listPlace = new ArrayList<HistoryPlace>();
		Cursor cur = db.query(TABLE_NAME_HIS, null, null, null, null, null,
				null);
		cur.moveToFirst();
		while (!cur.isAfterLast()) {
			listPlace.add(new HistoryPlace(cur.getInt(0), cur.getString(1), cur
					.getInt(2), cur.getString(3), cur.getString(4), cur
					.getDouble(5), cur.getDouble(6)));
			cur.moveToNext();
		}
		return listPlace;
	}

}
